package com.whzykj.model;

import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Record;
import com.whzykj.common.DateUtil;
import com.whzykj.common.DictCache;
import com.whzykj.jfinal.TableBind;

@TableBind(name = "returnbill", pk = "sn")
public class ReturnBill extends Model<ReturnBill> {
	private static final long serialVersionUID = 1L;
	public static final ReturnBill dao = new ReturnBill();

	/**
	 * 获取退赔类型的字符串
	 * 
	 * @return <option value="00">残次退赔</option><option
	 *         value="00">整件退赔</option><option value="00">二次转卖</option>
	 */
	public String getOptionsByKind(String kind) {
		StringBuffer str = new StringBuffer("");
		List<Dictionary> list = DictCache.cache.getDictListByType(kind);
		if (list != null && list.size() > 0) {
			for (Dictionary dict : list) {
				String diccode = dict.getStr("dict_code");
				String dicname = dict.getStr("dict_name");
				str.append("<option value=\"" + diccode + "\">");
				str.append(dicname);
				str.append("</option>");
			}
		}
		return str.toString();
	}

	/**
	 * 获取退赔客户的相关信息
	 * 
	 * @param custid
	 * @return
	 */
	public Record getReturnCustomer(int custid) {
		Record rec = null;
		StringBuffer sql = new StringBuffer(
				" select cust.CUST_ID,cust.CUST_NAME,cust.MOBILE,sta.STATION_ID,sta.STATION_NAME,acct.amount,acct.acct_id");
		sql.append(" FROM customer cust INNER JOIN station sta on cust.STATION_ID=sta.STATION_ID");
		sql.append(" INNER JOIN account acct on acct.cust_id=cust.CUST_ID");
		sql.append(" where cust.CUST_ID=" + custid);
		rec = Db.findFirst(sql.toString());
		return rec;
	}

	/**
	 * 获得指定客户在指定日期的购买商品信息的列表
	 * 
	 * @param orderdate
	 * @param custid
	 * @return
	 */
	public List<GoodsInfo> getReturnGoodsInfo(String orderdate, int custid) {
		List<GoodsInfo> list = null;
		StringBuffer sql = new StringBuffer(
				" select gi.goods_id,gi.goods_name ");
		sql.append(" from goodsinfo gi where gi.goods_id in");
		sql.append(" (SELECT DISTINCT(od.goods_id)");
		sql.append(" from orderinfo oi INNER JOIN orderdetails od ON oi.order_id=od.order_id");
		sql.append(" where TO_DAYS(oi.order_date)=TO_DAYS('" + orderdate
				+ "')  AND oi.STATUS<>'over' AND oi.cust_id=" + custid + ")");
		sql.append(" ORDER BY gi.goods_id");
		list = GoodsInfo.me.find(sql.toString());
		return list;
	}

	/**
	 * 获取退赔订单总金额
	 * 
	 * @param orderdate
	 * @param custid
	 * @return
	 */
	public double getReturnTotalsAmounts(String orderdate, int custid) {
		double amounts = 0;
		Record rec = Db
				.findFirst(
						"select SUM(oi.PAY_AMOUNTS) as amounts from orderinfo oi where oi.CUST_ID=?  AND oi.STATUS<>'over' and TO_DAYS(oi.ORDER_DATE)=TO_DAYS(?)",
						custid, orderdate);
		if (rec != null) {
			BigDecimal bd = rec.getBigDecimal("amounts");
			if (bd != null) {
				amounts = bd.doubleValue();
			}
		}
		return amounts;
	}

	/**
	 * 获取退赔订单的编号字符串
	 * 
	 * @param orderdate
	 * @param custid
	 * @return
	 */
	public String getReturnOrderIds(String orderdate, int custid,
			String goodsids) {
		StringBuffer sql = new StringBuffer(
				" select oi.ORDER_ID from orderinfo oi");
		sql.append(" INNER JOIN orderdetails od on oi.ORDER_ID=od. ORDER_ID");
		sql.append(" where oi.CUST_ID=? and TO_DAYS(oi.ORDER_DATE)=TO_DAYS(?) AND oi.STATUS<>'over' AND od.goods_id in(?)");
		List<OrderInfo> orders = OrderInfo.me.find(sql.toString(), custid,
				orderdate, goodsids);
		StringBuffer str = new StringBuffer("");
		if (orders != null && orders.size() > 0) {
			for (int i = 0; i < orders.size(); i++) {
				OrderInfo order = orders.get(i);
				if (i != orders.size() - 1) {
					str.append(order.getStr("ORDER_ID") + ",");
				} else {
					str.append(order.getStr("ORDER_ID"));
				}
			}
		}
		return str.toString();
	}

	/**
	 * 获得指定客户在指定日期的购买商品信息的选项列表信息
	 * 
	 * @param orderdate
	 * @param custid
	 * @return
	 */
	public String getReturnGoodsInfoStr(String orderdate, int custid) {
		StringBuffer str = new StringBuffer("");
		List<GoodsInfo> list = getReturnGoodsInfo(orderdate, custid);
		if (list != null && list.size() > 0) {
			for (GoodsInfo goodsInfo : list) {
				int goodsid = goodsInfo.getInt("goods_id");
				String goodsname = goodsInfo.getStr("goods_name");
				str.append("<option value=\"" + goodsid + "\">");
				str.append(goodsname);
				str.append("</option>");
			}
		}
		return str.toString();
	}

	/**
	 * 返回指定日期、指定客户购买指定商品的最大价格
	 * 
	 * @param orderdate
	 * @param custid
	 * @param goodsid
	 * @return
	 */
	public String getMaxPriceForReturn(String orderdate, int custid, int goodsid) {
		String str = null;
		java.text.DecimalFormat df = new java.text.DecimalFormat("#.00");
		StringBuffer sql = new StringBuffer(
				" SELECT MAX(sale_price) as maxprice");
		sql.append(" from orderinfo oi INNER JOIN orderdetails od ON oi.order_id=od.order_id");
		sql.append(" where TO_DAYS(oi.order_date)=TO_DAYS('" + orderdate
				+ "') AND oi.STATUS<>'over' AND oi.cust_id=" + custid
				+ " AND od.goods_id=" + goodsid);
		Record rec = Db.findFirst(sql.toString());
		if (rec == null) {
			return "￥0.00";
		} else {
			BigDecimal maxprice = rec.getBigDecimal("maxprice");
			if (maxprice == null) {
				return "￥0.00";
			} else {
				str = df.format(maxprice.doubleValue());
			}
		}
		return str;
	}

	/**
	 * 查找指定条件的退赔记录列表
	 * 
	 * @param startDate
	 * @param endDate
	 * @param stationid
	 * @param custname
	 * @return
	 */
	public List<Record> findReturnRecordList(String startDate, String endDate,
			Integer stationid, String custname) {
		List<Record> recList = null;
		StringBuffer condition = new StringBuffer("where rb.state='01' ");
		if (startDate == null || startDate.trim().equals("")) {
			startDate = DateUtil.getToday();
		}
		if (endDate == null || endDate.trim().equals("")) {
			endDate = DateUtil.getToday();
		}
		condition.append("AND TO_DAYS(rb.apply_time)>=TO_DAYS('" + startDate
				+ "') AND TO_DAYS(rb.apply_time)<TO_DAYS('" + endDate + "') ");
		if (stationid != null && stationid.intValue() != -1) {
			condition
					.append("AND sta.STATION_ID=" + stationid.intValue() + " ");
		}
		if (custname != null && !custname.trim().equals("")) {
			condition.append("AND cust.CUST_NAME LIKE '%" + custname.trim()
					+ "%' ");
		}
		StringBuffer sql = new StringBuffer(
				"select rb.sn,rb.apply_time,rb.order_date,rb.amount,rb.num,rb.apply_user,rb.cust_id,cust.CUST_NAME,");
		sql.append("cust.MOBILE,rb.kind,sta.STATION_NAME,ui.USERNAME ");
		sql.append("from returnbill rb INNER JOIN customer cust on cust.CUST_ID=rb.cust_id ");
		sql.append("INNER JOIN station sta on sta.STATION_ID=cust.STATION_ID ");
		sql.append("inner JOIN userinfo ui on ui.USERID=rb.admit_user ");
		sql.append(condition.toString());
		sql.append("order by rb.apply_time DESC,sta.STATION_NAME,rb.kind,cust.CUST_ID");
		recList = dealReturnRecordList(Db.find(sql.toString()));
		return recList;
	}

	/**
	 * 处理退赔列表中的退赔类型和金额显示
	 * 
	 * @param recList
	 * @return
	 */
	private List<Record> dealReturnRecordList(List<Record> recList) {
		if (recList == null || recList.size() == 0) {
			return recList;
		}
		List<Record> rList = new ArrayList<Record>();
		for (Record rec : recList) {
			rec.set("kind", dealDictKindInfo(rec.getStr("kind"), "RETURN_KIND"));
			rec.set("amount", "￥" + rec.getBigDecimal("amount").doubleValue());
			rec.set("num", rec.getInt("num") + "件");
			rec.set("order_date", rec.getStr("order_date").substring(0, 10));
			rList.add(rec);
		}
		return rList;
	}
	
	/**
	 * 处理退赔列表中的退赔类型和金额显示
	 * @param recList
	 * @return
	 */
	public List<ReturnBill> dealReturnBillList(List<ReturnBill> recList) {
		if (recList == null || recList.size() == 0) {
			return recList;
		}
		List<ReturnBill> rList = new ArrayList<ReturnBill>();
		for (ReturnBill rec : recList) {
			rec.set("kind", dealDictKindInfo(rec.getStr("kind"), "RETURN_KIND"));
			rec.set("amount", "￥" + rec.getBigDecimal("amount").doubleValue());
			rec.set("num", rec.getInt("num") + "件");
			rec.set("order_date", DateUtil.convertDateToString(rec.getDate("order_Date")));
			rList.add(rec);
		}
		return rList;
	}

	/**
	 * 处理退赔和问题类型的字符串
	 * 
	 * @param kind
	 * @param type
	 * @return
	 */
	private String dealDictKindInfo(String kind, String type) {
		if (kind == null || kind.trim().equals("")) {
			return "";
		}
		StringBuffer res = new StringBuffer();
		String[] kinds = kind.split(",");
		for (int i = 0, max = kinds.length; i < max; i++) {
			if (i == max - 1) {
				res.append(DictCache.cache.getDicByText(kinds[i], type));
			} else {
				res.append(DictCache.cache.getDicByText(kinds[i], type) + ",");
			}
		}
		return res.toString();
	}

	/**
	 * 查询退赔单明细信息
	 * 
	 * @param sn
	 * @return
	 */
	public List<Record> findReturnRecordDetails(String sn) {
		List<Record> recList = null;
		StringBuffer dsql = new StringBuffer(
				"select rd.goods_id,gi.APP_NAME,rd.goods_price,rd.kind,rd.num,rd.question_kind,");
		dsql.append("rd.amount,rb.apply_user,rb.apply_time,ui.USERNAME  ");
		dsql.append("from returnbill rb INNER JOIN returndetails rd on rb.sn=rd.sn ");
		dsql.append("INNER JOIN goodsinfo gi on gi.GOODS_ID=rd.goods_id ");
		dsql.append("INNER JOIN userinfo ui on ui.USERID=rb.apply_user ");
		dsql.append("where rb.sn='" + sn + "'");
		recList = Db.find(dsql.toString());
		if (recList != null && recList.size() > 0) {
			for (Record rec : recList) {
				rec.set("kind",
						dealDictKindInfo(rec.getStr("kind"), "RETURN_KIND"));
				rec.set("question_kind",
						dealDictKindInfo(rec.getStr("question_kind"),
								"QUESTION_KIND"));
				rec.set("goods_price", "￥"
						+ rec.getBigDecimal("goods_price").doubleValue());
				rec.set("amount", "￥"
						+ rec.getBigDecimal("amount").doubleValue());
				rec.set("num", rec.getInt("num") + "件");
			}
		}
		return recList;
	}

	/**
	 * 查找退赔信息
	 * 
	 * @param sn
	 * @return
	 */
	public Record findReturnRecordInfo(String sn) {
		StringBuffer sql = new StringBuffer(
				"select cust.cust_name,cust.mobile,sta.station_name,rb.kind,rb.reason,rb.images,rb.amount ");
		sql.append("from returnbill rb INNER JOIN customer cust on cust.cust_id=rb.cust_id ");
		sql.append("INNER JOIN station sta ON sta.station_id=cust.station_id ");
		sql.append("where sn='" + sn + "'");
		Record rec = Db.findFirst(sql.toString());
		if (rec != null) {
			rec.set("kind", dealDictKindInfo(rec.getStr("kind"), "RETURN_KIND"));
			String images=rec.getStr("images");
			if(images!=null && images.trim().length()>0){
				String[] imgs=images.split(";");
				if(imgs!=null && imgs.length>0){
					StringBuffer imgStr=new StringBuffer();
					for(String img:imgs){	
						imgStr.append("<a href='javascript:showImg(\""+img+"\")'><img height='150px' border='0' src='"+img+"'/></a>");
					}
					rec.set("images", imgStr.toString());
				}
			}
		}
		return rec;
	}

	/**
	 * 查询按站点退赔信息列表
	 * 
	 * @param startDate
	 * @param endDate
	 * @param stationid
	 * @return
	 */
	public List<Record> findReturnListByStation(String startDate,
			String endDate, Integer stationid) {
		List<Record> recList = null;
		StringBuffer condition = new StringBuffer("WHERE (rd.kind='00' or rd.kind='01') ");
		if (startDate != null && !startDate.trim().equals("")) {
			condition.append(" AND TO_DAYS(rb.order_date)>=TO_DAYS('"
					+ startDate + "')");
		}
		if (endDate != null && !endDate.trim().equals("")) {
			condition.append(" AND TO_DAYS(rb.order_date)<=TO_DAYS('" + endDate
					+ "')");
		}
		if (stationid != null && stationid.intValue() != -1) {
			condition.append(" AND sta.STATION_ID=" + stationid.intValue());
		}
		StringBuffer sql = new StringBuffer(
				"select sta.station_id,sta.station_name,ui.username,ui.mobile,rb.order_date,rd.kind,");
		sql.append("(select SUM(od.sale_num) from orderinfo oi ");
		sql.append("INNER JOIN orderdetails od on oi.order_id=od.order_id ");
		sql.append("INNER JOIN customer cust on cust.cust_id=oi.cust_id ");
		sql.append("INNER JOIN station sta1 on sta1.station_id=cust.station_id ");
		sql.append("where oi.STATUS<>'over' and TO_DAYS(oi.order_date)=TO_DAYS(rb.order_date) and sta1.station_id=sta.station_id) as ordernums,");
		sql.append("(select SUM(od.sale_num*od.sale_price) from orderinfo oi ");
		sql.append("INNER JOIN orderdetails od on oi.order_id=od.order_id ");
		sql.append("INNER JOIN customer cust on cust.cust_id=oi.cust_id ");
		sql.append("INNER JOIN station sta2 on sta2.station_id=cust.station_id ");
		sql.append("where oi.STATUS<>'over' and TO_DAYS(oi.order_date)=TO_DAYS(rb.order_date) and sta2.station_id=sta.station_id) as orderamounts,");
		sql.append("SUM(rd.num) as nums,SUM(rd.amount) as amounts from returnbill rb ");
		sql.append("INNER JOIN returndetails rd on rb.sn=rd.sn ");
		sql.append("INNER JOIN customer cust on cust.cust_id=rb.cust_id ");
		sql.append("INNER JOIN station sta on sta.station_id=cust.station_id ");
		sql.append("INNER JOIN userinfo ui on ui.userid=sta.STATION_MASTER ");
		sql.append(condition.toString());
		sql.append(" GROUP BY sta.station_name,rb.order_date,rd.kind");
		recList = Db.find(sql.toString());
		return dealReturnListTotalByStation(dealReturnListByStation(recList));
	}
	
	/**
	 * 处理退赔信息列表（按站点）：添加站点合计
	 * @param recList
	 * @return
	 */
	private List<Record> dealReturnListTotalByStation(List<Record> recList){	
		if(recList==null || recList.size()==0){
			return recList;
		}
		List<Record> rList=new ArrayList<Record>();
		DecimalFormat df=new DecimalFormat("#0.00");
		DecimalFormat df1 = new DecimalFormat("#0.0");
		Record r1=recList.get(0);
		Record r2=null;
		int totalCcNums=r1.getInt("ccnums");								// 站点汇总残次总件数
		double totalCcAmounts=r1.getDouble("ccamounts");					// 站点汇总残次退赔总金额
		int totalZjNums=r1.getInt("zjnums");								// 站点汇总整件退赔总件数
		double totalZjAmounts=r1.getDouble("zjamounts");					// 站点汇总整件退赔总金额
		BigDecimal totalONums=r1.getBigDecimal("ordernums");				// 站点汇总订单件数
		BigDecimal totalOAmounts=r1.getBigDecimal("orderamounts");			// 站点汇总订单销售总额
		Record rec=new Record();
		rec.set("station_name", r1.getStr("station_name")).set("username", r1.getStr("username")).set("mobile", r1.getStr("mobile")).set("order_date", r1.getStr("order_date"));
		rec.set("ordernums", r1.getBigDecimal("ordernums").intValue()+"件").set("orderamounts", "￥"+r1.getBigDecimal("orderamounts").toString());
		rec.set("ccnums", r1.getInt("ccnums")+"件").set("ccamounts", "￥"+df.format(r1.getDouble("ccamounts"))).set("ccrate", r1.getStr("ccrate"));
		rec.set("zjnums", r1.getInt("zjnums")+"件").set("zjamounts", "￥"+df.format(r1.getDouble("zjamounts"))).set("zjrate", r1.getStr("zjrate"));
		rList.add(rec);
		for(int i=1,size=recList.size();i<size;i++){
			r2=recList.get(i);
			if(!r1.getStr("station_name").equals(r2.getStr("station_name"))){
				rec=new Record();
				rec.set("station_name", "站点合计").set("username", "").set("mobile", "").set("order_date", "");
				rec.set("ordernums", totalONums.intValue()+"件").set("orderamounts", "￥"+totalOAmounts.toString());
				double ccrate = totalCcAmounts*100/ totalOAmounts.doubleValue();
				double zjrate = totalZjAmounts*100/ totalOAmounts.doubleValue();
				rec.set("ccnums", totalCcNums+"件").set("ccamounts", "￥"+df.format(totalCcAmounts)).set("ccrate", df1.format(ccrate) + "%");
				rec.set("zjnums", totalZjNums+"件").set("zjamounts", "￥"+df.format(totalZjAmounts)).set("zjrate", df1.format(zjrate) + "%");
				rList.add(rec);
				rec=null;
				totalCcNums=r2.getInt("ccnums");									// 站点汇总残次总件数
				totalCcAmounts=r2.getDouble("ccamounts");							// 站点汇总残次退赔总金额
				totalZjNums=r2.getInt("zjnums");									// 站点汇总整件退赔总件数
				totalZjAmounts=r2.getDouble("zjamounts");							// 站点汇总整件退赔总金额
				totalONums=r2.getBigDecimal("ordernums");							// 站点汇总订单件数
				totalOAmounts=r2.getBigDecimal("orderamounts");						// 站点汇总订单销售总额
			}else{
				totalCcNums=totalCcNums+r2.getInt("ccnums");
				totalCcAmounts=totalCcAmounts+r2.getDouble("ccamounts");
				totalZjNums=totalZjNums+r2.getInt("zjnums");
				totalZjAmounts=totalZjAmounts+r2.getDouble("zjamounts");
				totalONums=totalONums.add(r2.getBigDecimal("ordernums"));
				totalOAmounts=totalOAmounts.add(r2.getBigDecimal("orderamounts"));
			}
			rec=new Record();
			rec.set("station_name", r2.getStr("station_name")).set("username", r2.getStr("username")).set("mobile", r2.getStr("mobile")).set("order_date", r2.getStr("order_date"));
			rec.set("ordernums", r2.getBigDecimal("ordernums").intValue()+"件").set("orderamounts", "￥"+r2.getBigDecimal("orderamounts").toString());
			rec.set("ccnums", r2.getInt("ccnums")+"件").set("ccamounts", "￥"+df.format(r2.getDouble("ccamounts"))).set("ccrate", r2.getStr("ccrate"));
			rec.set("zjnums", r2.getInt("zjnums")+"件").set("zjamounts", "￥"+df.format(r2.getDouble("zjamounts"))).set("zjrate", r2.getStr("zjrate"));
			rList.add(rec);
			r1=r2;
		}
		rec=new Record();
		rec.set("station_name", "站点合计").set("username", "").set("mobile", "").set("order_date", "");
		rec.set("ordernums", totalONums.intValue()+"件").set("orderamounts", "￥"+totalOAmounts.toString());
		double ccrate = totalCcAmounts*100/ totalOAmounts.doubleValue();
		double zjrate = totalZjAmounts*100/ totalOAmounts.doubleValue();
		rec.set("ccnums", totalCcNums+"件").set("ccamounts", "￥"+df.format(totalCcAmounts)).set("ccrate", df1.format(ccrate) + "%");
		rec.set("zjnums", totalZjNums+"件").set("zjamounts", "￥"+df.format(totalZjAmounts)).set("zjrate", df1.format(zjrate) + "%");
		rList.add(rec);
		return rList;
	}

	/**
	 * 处理按站点退赔列表信息
	 * 
	 * @param recList
	 * @return
	 */
	private List<Record> dealReturnListByStation(List<Record> recList) {
		List<Record> rList = new ArrayList<Record>();
		if (recList != null && recList.size() > 0) {
			Record r1 = recList.get(0);
			Record r2 = null;
			int ccNums = 0;
			int zjNums = 0;
			double ccAmounts = 0;
			String ccRate = "";		
			Record record = new Record();
			record.set("station_name", r1.getStr("station_name")).set("username", r1.getStr("username")).set("mobile", r1.getStr("mobile"));
			record.set("order_date", DateUtil.convertDateToString(r1.getDate("order_date")));
			record.set("ordernums", r1.getBigDecimal("ordernums"));
			record.set("orderamounts", r1.getBigDecimal("orderamounts"));
			ccNums = r1.getBigDecimal("nums").intValue();
			ccAmounts = r1.getBigDecimal("amounts").doubleValue();
			double rrr = ccAmounts*100
					/ r1.getBigDecimal("orderamounts").doubleValue();
			DecimalFormat df = new DecimalFormat("#0.0");
			ccRate = df.format(rrr) + "%";
			boolean isCanci = true; // 判断record保存的是残次退赔还是整件退赔
			boolean isFirst = true; // 表示Record首次保存数据
			if (r1.getStr("kind").equals("00")) {
				record.set("ccnums", ccNums).set("ccamounts", ccAmounts)
						.set("ccrate", ccRate);
				isCanci = true;
			} else {
				record.set("zjnums", ccNums).set("zjamounts", ccAmounts)
						.set("zjrate", ccRate);
				isCanci = false;
			}
			
			for (int i = 1, size = recList.size(); i < size; i++) {
				r2 = recList.get(i);
				if (r1.getStr("station_name").equals(r2.getStr("station_name"))
						&& r1.getDate("order_date").getTime()==r2.getDate("order_date").getTime()) {
					// 同一个站点同一天，试用的是同一个Record对象
					ccNums = r2.getBigDecimal("nums").intValue();
					ccAmounts = r2.getBigDecimal("amounts").doubleValue();
					rrr = ccAmounts*100
							/ r2.getBigDecimal("orderamounts").doubleValue();
					ccRate = df.format(rrr) + "%";
					if (r2.getStr("kind").equals("00")) {
						record.set("ccnums", ccNums)
								.set("ccamounts", ccAmounts)
								.set("ccrate", ccRate);
					} else {
						record.set("zjnums", ccNums)
								.set("zjamounts", ccAmounts)
								.set("zjrate", ccRate);
					}
					isFirst = false; // 二次保存数据
					rList.add(record);
					record = null;
				} else{
					// 要么是站点的不同日期，要么是不同的站点
					if (isFirst) { // 如果前面保存的始终是第一条数据,补齐数据保存
						if (isCanci) {
							record.set("zjnums", 0).set("zjamounts", 0.0)
									.set("zjrate", "0%");
						} else {
							record.set("ccnums", 0).set("ccamounts", 0.0)
									.set("ccrate", "0%");
						}
						rList.add(record);
					}
					record = new Record();
					record.set("station_name", r2.getStr("station_name")).set("username", r2.getStr("username")).set("mobile", r2.getStr("mobile"));;
					record.set("order_date", DateUtil.convertDateToString(r2.getDate("order_date")));
					record.set("ordernums", r2.getBigDecimal("ordernums"));
					record.set("orderamounts", r2.getBigDecimal("orderamounts"));
					ccNums = r2.getBigDecimal("nums").intValue();
					ccAmounts = r2.getBigDecimal("amounts").doubleValue();
					rrr = ccAmounts*100
							/ r2.getBigDecimal("orderamounts").doubleValue();
					ccRate = df.format(rrr) + "%";
					if (r2.getStr("kind").equals("00")) {
						record.set("ccnums", ccNums).set("ccamounts", ccAmounts)
								.set("ccrate", ccRate);
						isCanci = true;
					} else {
						record.set("zjnums", ccNums).set("zjamounts", ccAmounts)
								.set("zjrate", ccRate);
						isCanci = false;
					}
					isFirst = true;
				}
				r1 = r2;
			}
			if (isFirst) { // 如果前面保存的始终是第一条数据,补齐数据保存
				if (isCanci) {
					record.set("zjnums", 0).set("zjamounts", 0.0)
							.set("zjrate", "0%");
				} else {
					record.set("ccnums", 0).set("ccamounts", 0.0)
							.set("ccrate", "0%");
				}
				rList.add(record);
			}
		}
		return rList;
	}
	
	/**
	 * 查询退赔报表（按SKU）
	 * @param startDate
	 * @param endDate
	 * @param goodsid
	 * @param questionkind
	 * @return
	 */
	public List<Record> findReturnListBySku(String startDate,String endDate,Integer goodsid,String questionkind){
		List<Record> reList=null;
		StringBuffer condition=new StringBuffer("WHERE (rd.kind='00' or rd.kind='01') ");
		if(startDate==null || startDate.trim().equals("")){
			startDate=DateUtil.getToday();
		}
		condition.append("AND TO_DAYS(rb.order_date)>=TO_DAYS('"+startDate+"') ");
		if(endDate==null || endDate.trim().equals("")){
			endDate=DateUtil.getToday();
		}
		condition.append("AND TO_DAYS(rb.order_date)<=TO_DAYS('"+endDate+"') ");
		if(goodsid!=null && goodsid.intValue()!=-1){
			condition.append("AND rd.goods_id="+goodsid.intValue()+" ");
		}
		if(questionkind!=null && !questionkind.trim().equals("-1")){
			condition.append("AND rd.question_kind='"+questionkind+"' ");
		}
		StringBuffer sql=new StringBuffer("select rd.goods_id,gi.goods_name,rd.question_kind,rd.kind,SUM(rd.num) as nums,SUM(rd.amount) as amounts,");
		sql.append("(select SUM(od.sale_num) from orderinfo oi INNER JOIN orderdetails od on oi.order_id=od.order_id ");
		sql.append("where oi.STATUS<>'over' and TO_DAYS(oi.order_date)>=TO_DAYS('"+startDate+"') AND TO_DAYS(oi.order_date)<=TO_DAYS('"+endDate+"') and od.goods_id=rd.goods_id) as ordernums,");
		sql.append("(select SUM(od.sale_num*od.sale_price) from orderinfo oi INNER JOIN orderdetails od on oi.order_id=od.order_id ");
		sql.append("where oi.STATUS<>'over' and TO_DAYS(oi.order_date)>=TO_DAYS('"+startDate+"') AND TO_DAYS(oi.order_date)<=TO_DAYS('"+endDate+"') and od.goods_id=rd.goods_id) as orderamounts ");
		sql.append("from returnbill rb INNER JOIN returndetails rd on rb.sn=rd.sn INNER JOIN goodsinfo gi on gi.goods_id=rd.goods_id ");
		sql.append(condition.toString());
		sql.append(" GROUP BY rd.goods_id,rd.question_kind,rd.kind");
		reList=Db.find(sql.toString());
		List<Record> recList=dealReturnListBySku(reList,startDate,endDate);
		recList=dealReturnListTotalBySku(recList);
		return recList;
	}
	
	/**
	 * 处理SKU退赔报表，添加合计信息
	 * @param recList
	 * @return
	 */
	private List<Record> dealReturnListTotalBySku(List<Record> recList){
		if(recList==null || recList.size()==0){
			return recList;
		}
		List<Record> rList=new ArrayList<Record>();
		DecimalFormat df=new DecimalFormat("#0.00");
		DecimalFormat df1 = new DecimalFormat("#0.0");
		Record r1=recList.get(0);
		Record r2=null;
		BigDecimal tccnums=r1.getBigDecimal("ccnums");
		BigDecimal tccamounts=r1.getBigDecimal("ccamounts");
		BigDecimal tzjnums=r1.getBigDecimal("zjnums");
		BigDecimal tzjamounts=r1.getBigDecimal("zjamounts");
		String rate=null;
		Record rec=new Record();
		rec.set("goods_name", r1.getStr("goods_name")).set("date", r1.getStr("date")).set("ordernums", r1.getBigDecimal("ordernums").intValue()+"件").set("orderamounts", "￥"+r1.getBigDecimal("orderamounts").toString());
		rec.set("question_kind", r1.getStr("question_kind")).set("question_kinds", r1.getStr("question_kinds"));
		rec.set("ccnums", r1.getBigDecimal("ccnums").intValue()+"件").set("ccamounts", "￥"+r1.getBigDecimal("ccamounts").toString()).set("ccrate", r1.getStr("ccrate"));
		rec.set("zjnums", r1.getBigDecimal("zjnums").intValue()+"件").set("zjamounts", "￥"+r1.getBigDecimal("zjamounts").toString()).set("zjrate", r1.getStr("zjrate"));
		rList.add(rec);
		for(int i=1,size=recList.size();i<size;i++){
			r2=recList.get(i);
			if(r1.getInt("goods_id").intValue()!=r2.getInt("goods_id").intValue()){
				rec=new Record();
				rec.set("goods_name", r1.getStr("goods_name")).set("date", r1.getStr("date")).set("ordernums", "合计").set("orderamounts", "￥"+r1.getBigDecimal("orderamounts").toString());
				rec.set("question_kind", "").set("question_kinds", "");
				rate=df1.format(tccamounts.doubleValue()*100/r1.getBigDecimal("orderamounts").doubleValue())+"%";
				rec.set("ccnums", tccnums.intValue()+"件").set("ccamounts", "￥"+tccamounts.toString()).set("ccrate", rate);
				rate=df1.format(tzjamounts.doubleValue()*100/r1.getBigDecimal("orderamounts").doubleValue())+"%";
				rec.set("zjnums", tzjnums.intValue()+"件").set("zjamounts", "￥"+tzjamounts.toString()).set("zjrate", rate);
				rList.add(rec);
				rec=null;
				tccnums=r2.getBigDecimal("ccnums");
				tccamounts=r2.getBigDecimal("ccamounts");
				tzjnums=r2.getBigDecimal("zjnums");
				tzjamounts=r2.getBigDecimal("zjamounts");
			}else{
				tccnums=tccnums.add(r2.getBigDecimal("ccnums"));
				tccamounts=tccamounts.add(r2.getBigDecimal("ccamounts"));
				tzjnums=tzjnums.add(r2.getBigDecimal("zjnums"));
				tzjamounts=tzjamounts.add(r2.getBigDecimal("zjamounts"));
			}
			rec=new Record();
			rec.set("goods_name", r2.getStr("goods_name")).set("date", r2.getStr("date")).set("ordernums", r2.getBigDecimal("ordernums").intValue()+"件").set("orderamounts", "￥"+r2.getBigDecimal("orderamounts").toString());
			rec.set("question_kind", r2.getStr("question_kind")).set("question_kinds", r2.getStr("question_kinds"));
			rec.set("ccnums", r2.getBigDecimal("ccnums").intValue()+"件").set("ccamounts", "￥"+r2.getBigDecimal("ccamounts").toString()).set("ccrate", r2.getStr("ccrate"));
			rec.set("zjnums", r2.getBigDecimal("zjnums").intValue()+"件").set("zjamounts", "￥"+r2.getBigDecimal("zjamounts").toString()).set("zjrate", r2.getStr("zjrate"));
			rList.add(rec);
			r1=r2;
		}
		rec=new Record();
		rec.set("goods_name", r1.getStr("goods_name")).set("date", r1.getStr("date")).set("ordernums", "合计").set("orderamounts", "￥"+r1.getBigDecimal("orderamounts").toString());
		rec.set("question_kind", "").set("question_kinds", "");
		rate=df1.format(tccamounts.doubleValue()*100/r1.getBigDecimal("orderamounts").doubleValue())+"%";
		rec.set("ccnums", tccnums.intValue()+"件").set("ccamounts", "￥"+tccamounts.toString()).set("ccrate", rate);
		rate=df1.format(tzjamounts.doubleValue()*100/r1.getBigDecimal("orderamounts").doubleValue())+"%";
		rec.set("zjnums", tzjnums.intValue()+"件").set("zjamounts", "￥"+tzjamounts.toString()).set("zjrate", rate);
		rList.add(rec);
		return rList;
	}
	
	/**
	 * 处理按SKU退赔报表，合并信息
	 * @param recList
	 * @return
	 */
	private List<Record> dealReturnListBySku(List<Record> recList,String startDate,String endDate){
		if(recList==null || recList.size()==0){
			return recList;
		}
		List<Record> rList=new ArrayList<Record>();
		Record r1=recList.get(0);
		Record r2=null;
		BigDecimal nums=r1.getBigDecimal("nums");
		BigDecimal amounts=r1.getBigDecimal("amounts");
		BigDecimal ordernums=r1.getBigDecimal("ordernums");
		BigDecimal orderamounts=r1.getBigDecimal("orderamounts");
		DecimalFormat df=new DecimalFormat("#0.0");
		String rate=df.format(amounts.doubleValue()*100/orderamounts.doubleValue())+"%";
		Record rec=new Record();
		rec.set("goods_name", r1.getStr("goods_name")).set("date", startDate+"~"+endDate).set("ordernums", ordernums).set("orderamounts", orderamounts).set("question_kind", r1.getStr("question_kind"));
		rec.set("question_kinds", DictCache.cache.getDicByText(r1.getStr("question_kind"), "QUESTION_KIND")).set("goods_id", r1.getInt("goods_id"));
		boolean isFirst=true;
		boolean isCanci=true;
		if(r1.getStr("kind").equals("00")){
			rec.set("ccnums", nums).set("ccamounts", amounts).set("ccrate", rate);
			isCanci=true;
		}else{
			rec.set("zjnums", nums).set("zjamounts", amounts).set("zjrate", rate);
			isCanci=false;
		}
		for(int i=1,size=recList.size();i<size;i++){
			r2=recList.get(i);
			if(r1.getInt("goods_id").intValue()==r2.getInt("goods_id").intValue() && r1.getStr("question_kind").equals(r2.getStr("question_kind"))){
				nums=r2.getBigDecimal("nums");
				amounts=r2.getBigDecimal("amounts");
				rate=df.format(amounts.doubleValue()*100/orderamounts.doubleValue())+"%";
				if (r2.getStr("kind").equals("00")) {
					rec.set("ccnums", nums).set("ccamounts", amounts).set("ccrate", rate);
				}else{
					rec.set("zjnums", nums).set("zjamounts", amounts).set("zjrate", rate);
				}
				isFirst=false;
				rList.add(rec);
				rec=null;
			}else{
				if(isFirst){
					if(isCanci){
						rec.set("zjnums", BigDecimal.ZERO).set("zjamounts", BigDecimal.ZERO).set("zjrate", "0%");
					}else{
						rec.set("ccnums", BigDecimal.ZERO).set("ccamounts", BigDecimal.ZERO).set("ccrate", "0%");
					}
					rList.add(rec);
				}
				rec=new Record();
				nums=r2.getBigDecimal("nums");
				amounts=r2.getBigDecimal("amounts");
				ordernums=r2.getBigDecimal("ordernums");
				orderamounts=r2.getBigDecimal("orderamounts");
				rate=df.format(amounts.doubleValue()*100/orderamounts.doubleValue())+"%";
				rec.set("goods_name", r2.getStr("goods_name")).set("date", startDate+"~"+endDate).set("ordernums", ordernums).set("orderamounts", orderamounts).set("question_kind", r2.getStr("question_kind"));
				rec.set("question_kinds", DictCache.cache.getDicByText(r2.getStr("question_kind"), "QUESTION_KIND")).set("goods_id", r2.getInt("goods_id"));
				if(r2.getStr("kind").equals("00")){
					rec.set("ccnums", nums).set("ccamounts", amounts).set("ccrate", rate);
					isCanci=true;
				}else{
					rec.set("zjnums", nums).set("zjamounts", amounts).set("zjrate", rate);
					isCanci=false;
				}
				isFirst=true;
			}
			r1 = r2;
		}
		if(isFirst){
			if(isCanci){
				rec.set("zjnums", BigDecimal.ZERO).set("zjamounts", BigDecimal.ZERO).set("zjrate", "0%");
			}else{
				rec.set("ccnums", BigDecimal.ZERO).set("ccamounts", BigDecimal.ZERO).set("ccrate", "0%");
			}
			rList.add(rec);
		}
		return rList;
	}

}
